-- 需求：网点自动化（迁移修订 增加集散点和建包）
-- 开发：侯文龙
-- 日期：2022-09-07
with t_network as (--网点资料
            select
                      code  --网点code
                     ,name      --网点名字
                     ,agent_id  --代理区id
                     ,agent_code --代理区code
                     ,agent_name --代理区name
                     ,fran_code as franchisee_code  --加盟商code
                     ,fran_name as franchisee_name --加盟商名字
                       --station_type--1 中心 2 集散 3 网点
                     ,network_type  as station_type   --'网点类型:1-总部,2-代理区,3-加盟商,4-中心 ,5-集散点,6-网点'
                  from jms_dim.dim_network_whole_massage
                  where network_type in(5,6)
),
    t_waybill as (--20天运单数据,按运单号去重,获取派件网点
          select waybill_no as billcode,
                 dispatch_network_code
          from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
          where dt between date_format(date_add('{{ execution_date | cst_ds }}', -30), 'yyyy-MM-dd')
            and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
),
    t_start_center as (
    select billcode,
           scantime
    from (
          select billcode,
                 scantime,
                 row_number() over (partition by billcode order by scantime asc) bill_rank
          from (
                select waybill_no as billcode,
                       scan_time as scantime
                from jms_dwd.dwd_tab_barscan_centersend_base_dt
                where dt between date_format(date_add('{{ execution_date | cst_ds }}', -30), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                  and length(waybill_no) = 15
                  and length(scan_site_code) > 0
                union all
                select waybill_no as billcode,
                      scan_time as scantime
                from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                where dt between date_format(date_add('{{ execution_date | cst_ds }}', -30), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                  and length(waybill_no) = 15
                  and length(scan_site_code) > 0
                ) t
          ) t where bill_rank = 1
)

 insert overwrite table jms_dm.dm_network_automation_rate_dt partition(dt)
 --网点自动化需求二期
select
       nvl(t_network1.agent_code,'-') as agent_code   --代理区编码
       ,nvl(t_network1.agent_name,'-') as agent_name   --代理区名称
       ,nvl(t_network1.franchisee_code,'-') as franchisee_code   --加盟商编码
       ,nvl(t_network1.franchisee_name,'-') as franchisee_name   --加盟商名称
       ,scansitecode as network_code   --网点编码
       ,nvl(t_network1.name,'-') as network_name   --网点名称
       ,output_num   --出件-出件量
       ,output_auto_num   --出件-自动化扫描量
       ,output_pc_num   --出件-PC端扫描量
       ,output_smart_num   --出件-智能设备扫描量
       ,output_auto_sort_num   --出件-自动分练扫描量
       ,output_common_num   --出件-共配扫描量
       ,dispatch_num   --派件-派件量
       ,dispatch_auto_num   --派件-自动化扫描量
       ,dispatch_pc_num   --派件-PC端扫描量
       ,null as dispatch_smart_num   --派件-智能设备扫描量
       ,dispatch_auto_sort_num   --派件-自动分练扫描量
       ,dispatch_common_num   --派件-共配扫描量
       ,scan_date   --扫描日期
       ,t_network1.station_type    -- 5 集散  6网点
       ,scan_date as dt
from (
      select scan_date,
             scansitecode,
             sum(case when output_scan_time is not null then 1 else 0 end) as output_num,
             sum(case when output_auto_scan_time is not null then 1 else 0 end) as output_auto_num,
             sum(case when output_pc_scan_time is not null then 1 else 0 end) as output_pc_num,
             sum(case when output_smart_scan_time is not null then 1 else 0 end) as output_smart_num,
             sum(case when output_auto_sort_scan_time is not null then 1 else 0 end) as output_auto_sort_num,
             sum(case when output_common_scan_time is not null then 1 else 0 end) as output_common_num,
             sum(case when dispatch_scan_time is not null then 1 else 0 end) as dispatch_num,
             sum(case when dispatch_auto_scan_time is not null then 1 else 0 end) as dispatch_auto_num,
             sum(case when dispatch_pc_scan_time is not null then 1 else 0 end) as dispatch_pc_num,
             null as dispatch_smart_num,
             sum(case when dispatch_auto_sort_scan_time is not null then 1 else 0 end) as dispatch_auto_sort_num,
             sum(case when dispatch_common_scan_time is not null then 1 else 0 end) as dispatch_common_num
      from (
            select billcode,
                   scansitecode,
                   date_format(scantime, 'yyyy-MM-dd') as scan_date,
                   first_value(output_scan_time) over (partition by billcode,scansitecode order by output_scan_time asc nulls last) output_scan_time,
                  -- first_value(output_scan_type) over (partition by billcode,scansitecode order by output_scan_time asc nulls last) output_scan_type,
                  -- first_value(output_source) over (partition by billcode,scansitecode order by output_scan_time asc nulls last) output_source,
                   first_value(output_auto_scan_time) over (partition by billcode,scansitecode order by output_auto_scan_time asc nulls last) output_auto_scan_time,
                  -- first_value(output_auto_scan_type) over (partition by billcode,scansitecode order by output_auto_scan_time asc nulls last) output_auto_scan_type,
                  -- first_value(output_auto_source) over (partition by billcode,scansitecode order by output_auto_scan_time asc nulls last) output_auto_source,
                   first_value(output_pc_scan_time) over (partition by billcode,scansitecode order by output_pc_scan_time asc nulls last) output_pc_scan_time,
                   --first_value(output_pc_scan_type) over (partition by billcode,scansitecode order by output_pc_scan_time asc nulls last) output_pc_scan_type,
                   first_value(output_smart_scan_time) over (partition by billcode,scansitecode order by output_smart_scan_time asc nulls last) output_smart_scan_time,
                  -- first_value(output_smart_scan_type) over (partition by billcode,scansitecode order by output_smart_scan_time asc nulls last) output_smart_scan_type,
                   --first_value(output_smart_source) over (partition by billcode,scansitecode order by output_smart_scan_time asc nulls last) output_smart_source,
                   first_value(output_auto_sort_scan_time) over (partition by billcode,scansitecode order by output_auto_sort_scan_time asc nulls last) output_auto_sort_scan_time,
                  -- first_value(output_auto_sort_scan_type) over (partition by billcode,scansitecode order by output_auto_sort_scan_time asc nulls last) output_auto_sort_scan_type,
                   first_value(output_common_scan_time) over (partition by billcode,scansitecode order by output_common_scan_time asc nulls last) output_common_scan_time,
                  -- first_value(output_common_scan_type) over (partition by billcode,scansitecode order by output_common_scan_time asc nulls last) output_common_scan_type,
                   first_value(dispatch_scan_time) over (partition by billcode,scansitecode order by dispatch_scan_time asc nulls last) dispatch_scan_time,
                  -- first_value(dispatch_scan_type) over (partition by billcode,scansitecode order by dispatch_scan_time asc nulls last) dispatch_scan_type,
                   --first_value(dispatch_source) over (partition by billcode,scansitecode order by dispatch_scan_time asc nulls last) dispatch_source,
                   first_value(dispatch_auto_scan_time) over (partition by billcode,scansitecode order by dispatch_auto_scan_time asc nulls last) dispatch_auto_scan_time,
                   --first_value(dispatch_auto_scan_type) over (partition by billcode,scansitecode order by dispatch_auto_scan_time asc nulls last) dispatch_auto_scan_type,
                  -- first_value(dispatch_auto_source) over (partition by billcode,scansitecode order by dispatch_auto_scan_time asc nulls last) dispatch_auto_source,
                   first_value(dispatch_pc_scan_time) over (partition by billcode,scansitecode order by dispatch_pc_scan_time asc nulls last) dispatch_pc_scan_time,
                  -- first_value(dispatch_pc_scan_type) over (partition by billcode,scansitecode order by dispatch_pc_scan_time asc nulls last) dispatch_pc_scan_type,
                  --first_value(dispatch_smart_scan_time) over (partition by billcode,scansitecode order by dispatch_smart_scan_time asc nulls last) dispatch_smart_scan_time,
                 --  first_value(dispatch_smart_scan_type) over (partition by billcode,scansitecode order by dispatch_smart_scan_time asc nulls last) dispatch_smart_scan_type,
                  -- first_value(dispatch_smart_source) over (partition by billcode,scansitecode order by dispatch_smart_scan_time asc nulls last) dispatch_smart_source,
                   first_value(dispatch_auto_sort_scan_time) over (partition by billcode,scansitecode order by dispatch_auto_sort_scan_time asc nulls last) dispatch_auto_sort_scan_time,
                  -- first_value(dispatch_auto_sort_scan_type) over (partition by billcode,scansitecode order by dispatch_auto_sort_scan_time asc nulls last) dispatch_auto_sort_scan_type,
                   first_value(dispatch_common_scan_time) over (partition by billcode,scansitecode order by dispatch_common_scan_time asc nulls last) dispatch_common_scan_time,
                   --first_value(dispatch_common_scan_type) over (partition by billcode,scansitecode order by dispatch_common_scan_time asc nulls last) dispatch_common_scan_type,
                   row_number() over (partition by billcode,scansitecode order by scantime asc) bill_rank
            from (
                  select billcode,
                         scansitecode,
                         scantime,
                         case when scantype in ('快件揽收','入仓扫描','建包扫描','集货到件') then scantime else null end output_scan_time,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') then scantype else null end output_scan_type,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') then source else null end output_source,
                         case when scantype in ('入仓扫描','建包扫描','集货到件') and source in ('PC端','自动分拣设备') then scantime else null end output_auto_scan_time,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source in ('PC端','自动分拣设备') then scantype else null end output_auto_scan_type,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source in ('PC端','自动分拣设备') then source else null end output_auto_source,
                         case when scantype in ('快件揽收','入仓扫描','建包扫描','集货到件') and source = 'PC端' then scantime else null end output_pc_scan_time,
                        -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source = 'PC端' then scantype else null end output_pc_scan_type,
                         case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source in ('自动分拣设备','共配') then scantime else null end output_smart_scan_time,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source in ('自动分拣设备','共配') then scantype else null end output_smart_scan_type,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source in ('自动分拣设备','共配') then source else null end output_smart_source,
                         case when scantype in ('入仓扫描','建包扫描','集货到件') and source = '自动分拣设备' then scantime else null end output_auto_sort_scan_time,
                         --case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source = '自动分拣设备' then scantype else null end output_auto_sort_scan_type,
                         case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source = '共配' then scantime else null end output_common_scan_time,
                         -- case when scantype in ('快件揽收','入仓扫描','发件扫描','建包扫描','集货到件') and source = '共配' then scantype else null end output_common_scan_type,
                         case when scantype in ('到件扫描','出仓扫描','快件签收') then scantime else null end dispatch_scan_time,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') then scantype else null end dispatch_scan_type,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') then source else null end dispatch_source,
                         case when scantype in ('到件扫描') and source in ('PC端','自动分拣设备','共配') then scantime else null end dispatch_auto_scan_time,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source in ('PC端','自动分拣设备','共配') then scantype else null end dispatch_auto_scan_type,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source in ('PC端','自动分拣设备','共配') then source else null end dispatch_auto_source,
                         case when scantype in ('到件扫描') and source = 'PC端' then scantime else null end dispatch_pc_scan_time,
                         -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source = 'PC端' then scantype else null end dispatch_pc_scan_type,
                         case when scantype in ('到件扫描') and source in ('自动分拣设备','共配') then scantime else null end dispatch_smart_scan_time,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source in ('自动分拣设备','共配') then scantype else null end dispatch_smart_scan_type,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source in ('自动分拣设备','共配') then source else null end dispatch_smart_source,
                         case when scantype in ('到件扫描') and source = '自动分拣设备' then scantime else null end dispatch_auto_sort_scan_time,
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source = '自动分拣设备' then scantype else null end dispatch_auto_sort_scan_type,
                         case when scantype in ('到件扫描') and source = '共配' then scantime else null end dispatch_common_scan_time
                        -- case when scantype in ('到件扫描','出仓扫描','快件签收') and source = '共配' then scantype else null end dispatch_common_scan_type
                  from (
                        select t_start_network.billcode,
                                scansitecode,
                                t_start_network.scantime,
                                scantype,
                                nvl(source,'-') as source
                        from (
                            --揽收
                              select waybill_no as billcode,
                                     scan_site_code as scansitecode,
                                     scan_time as scantime,
                                     scan_type as scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_taking_base_dt
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                              union all
                              --入仓
                              select waybill_no as billcode,
                                     scan_site_code as scansitecode,
                                     scan_time as scantime,
                                     scan_type as scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_warehousing_base_dt
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                              union all
                              --网点发
                              select waybill_no as billcode,
                                    scan_site_code as scansitecode,
                                    scan_time as  scantime,
                                     scan_type as scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_sitesend_base_dt
                              left join t_network t_network1 on scan_site_code = t_network1.code
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                                -- and t_network1.station_type = 3
                            union all
                               --建包
                              select
                                 waybill_no as billcode
                                ,scan_site_code as scansitecode
                                ,scan_time as scantime
                                ,scan_type as scantype
                                ,source
                         from jms_dwd.dwd_tab_barscan_bagging_base_dt a --建包
                              left join t_network on a.scan_site_code=t_network.code
                         where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                                and t_network.station_type=5
                                --新增集货到件
                                union all
                                select waybill_no as billcode,
                                       scan_site_code as scansitecode,
                                       scan_time as scantime,
                                       scan_type as scantype,
                                       source
                                 from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                                 left join t_network t_network1 on scan_site_code = t_network1.code
                                 where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                   and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                   and length(waybill_no) = 15
                                   and length(scan_site_code) > 0
                                   and scan_type='集货到件'
                           ) t_start_network left join t_start_center on t_start_network.billcode = t_start_center.billcode
                        where t_start_network.scantime <= nvl(t_start_center.scantime,'9999-99-99')
                        union all
                        select t_end_network.billcode,
                               scansitecode,
                               scantime,
                               scantype,
                               nvl(source,'-') as source
                        from (
                              select waybill_no as billcode,
                                    scan_site_code as scansitecode,
                                     scan_time as scantime,
                                    scan_type as scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                              left join t_network t_network1 on scan_site_code = t_network1.code
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                                --and t_network1.station_type = 3
                              union all
                              --派件
                              select waybill_no as billcode,
                                    scan_site_code as  scansitecode,
                                    scan_time as  scantime,
                                    scan_type as scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_deliver_base_dt
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(scan_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(scan_site_code) > 0
                              --签收
                              union all
                              select waybill_no as billcode,
                                     signed_site_code as scansitecode,
                                     signed_time as scantime,
                                     '快件签收' scantype,
                                     source
                              from jms_dwd.dwd_tab_barscan_sign_base_dt
                              where dt = date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                                and to_date(signed_time) between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
                                and length(waybill_no) = 15
                                and length(signed_site_code) > 0
                            ) t_end_network
                            left join t_waybill
                            on t_end_network.billcode = t_waybill.billcode
                            left join t_network
                            on t_end_network.scansitecode=t_network.code
                            where if( t_network.station_type=5,t_end_network.scansitecode != t_waybill.dispatch_network_code,t_end_network.scansitecode = t_waybill.dispatch_network_code)
                        ) t
                  ) t
           ) t where bill_rank = 1
            group by scan_date,scansitecode
) t left join t_network t_network1 on scansitecode = t_network1.code
where t_network1.agent_code <> 'Test001'
distribute by  10
;
